<?php

namespace App\Exports;

use App\Exports\Traits\ExcelExportTrait;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class FinanceBillDetailExport
{
    use ExcelExportTrait;

    public function exportBillDetailExcel($bill){
        //文件名
        $excelName=$bill->company_name.'账单导出'.'_'.$bill->id.'_'.date('YmdHis').'.xlsx';
        //存储目录
        $excelStorePath='public/export/'.date('Y').'/'.date('m');
        Storage::makeDirectory($excelStorePath);
        $excelStoreName=storage_path('app').'/'.$excelStorePath.'/'.$excelName;
        //生成excel文件
        $res=$this->generateExcelFile($bill,$excelStoreName);

        //web读取目录
        $excelWebPath='storage/export/'.date('Y').'/'.date('m');
        return config('app.url').$excelWebPath.'/'.$excelName;
    }
    //生成excel文件
    public function generateExcelFile($bill,$excelStoreName){
        // 实例化 Spreadsheet 对象
        $spreadSheet = new Spreadsheet();
        // 获取活动工作薄
        $activeSheet = $spreadSheet->getActiveSheet();
        //默认
        $this->setExcelDefault($activeSheet);
        $this->setColumnAutoWidth($activeSheet);

        //表格标题
        $this->setTitle($activeSheet,$bill);

        //基本信息
        $this->basicInfo($activeSheet,$bill);

        //表头
        $this->tableHeads($activeSheet);

        //数据
        $accountBills=$bill->accountBills;
        $totalDataRows=count($accountBills);
        if($totalDataRows){
            $this->subBillInfo($activeSheet,$bill,$accountBills,$totalDataRows);
        }

        //底部
        $this->bottomInfo($activeSheet,$bill,$totalDataRows);

        //Xlsx类 将电子表格保存到文件
        $writer = new Xlsx($spreadSheet);
        $writer->save($excelStoreName);
        return true;
    }
    //表格标题
    public function setTitle($activeSheet,$bill){
        $topTitle='义乌优服网络科技有限公司 '.$bill->bill_month.' 客户对账单';
        $activeSheet->mergeCells('a1:m1');
        $activeSheet->setCellValue('a1',$topTitle);
        $this->setTitleStyle($activeSheet,'a1:m1');
    }
    //基本信息
    public function basicInfo($activeSheet,$bill){
        $activeSheet->setCellValue('a2','客户名：');
        $activeSheet->setCellValue('b2',$bill->company_name);
        $activeSheet->setCellValue('c2','状态：');
        $billStatus=$this->getBillStatus($bill->bill_status).' '.$this->getClearStatus($bill->clear_status);
        $activeSheet->setCellValue('d2',$billStatus);
        $activeSheet->setCellValue('a3','账期类型：');
        $billType=$bill->bill_type==1?'自然月':'非自然月';
        $activeSheet->setCellValue('b3',$billType);
        $activeSheet->setCellValue('c3','账单期：');
        $billPeriod=$bill->bill_period_start.' — '.$bill->bill_period_end;
        $activeSheet->setCellValue('d3',$billPeriod);
        $activeSheet->setCellValue('e3','结算群：');
        $activeSheet->setCellValue('f3',$bill->settlement_group);
        //
        $activeSheet->setCellValue('a5',$this->getTopDesc($bill));
    }
    //
    public function tableHeads($activeSheet){
        $headRow1=7;
        $headRow2=8;

        $activeSheet->mergeCells('a'.$headRow1.':g'.$headRow1);
        $activeSheet->setCellValue('a'.$headRow1,'服务项');

        $activeSheet->mergeCells('h'.$headRow1.':h'.$headRow2);
        $activeSheet->setCellValue('h'.$headRow1,'收费标准项');

        $activeSheet->mergeCells('i'.$headRow1.':p'.$headRow1);
        $activeSheet->setCellValue('i'.$headRow1,'费用项');

        $activeSheet->mergeCells('q'.$headRow1.':q'.$headRow2);
        $activeSheet->setCellValue('q'.$headRow1,'本期费用合计①');

        $activeSheet->mergeCells('r'.$headRow1.':r'.$headRow2);
        $activeSheet->setCellValue('r'.$headRow1,'上期应收金额②');

        $activeSheet->mergeCells('s'.$headRow1.':v'.$headRow1);
        $activeSheet->setCellValue('s'.$headRow1,'回款项③');

        $activeSheet->mergeCells('w'.$headRow1.':w'.$headRow2);
        $activeSheet->setCellValue('w'.$headRow1,'本期应收客户金额=①+②-③');

        $activeSheet->mergeCells('x'.$headRow1.':x'.$headRow2);
        $activeSheet->setCellValue('x'.$headRow1,'备注');
        //
        foreach ($this->columnHeads() as $k=>$head){
            $activeSheet->setCellValue($k.$headRow2,$head);
        }

    }
    public function columnHeads(){
        return [
            'a'=>'店铺名称',
            'b'=>'客服子账号名称',
            'c'=>'子账号服务日期',
            'd'=>'服务天数',
            'e'=>'平台接待数',
            'f'=>'总回复量',
            'g'=>'日均客服回复量',
            'i'=>'本期服务报价核算',
            'j'=>'店铺管理费/其他',
            'k'=>'加班费',
            'l'=>'销售额',
            'm'=>'退款额',
            'n'=>'实际销售额',
            'o'=>'提成比率',
            'p'=>'提成',
            's'=>'拼多多平台已收',
            't'=>'上期留抵金额',
            'u'=>'保证金抵扣',
            'v'=>'优惠金额',
        ];
    }
    public function horizontalColumns(){
        return ['a','h','i','o','p','q','u','v'];
    }

    //数据
    public function subBillInfo($activeSheet,$bill,$accountBills,$totalDataRows){
        $dataStartRow=9;
        $row=$dataStartRow;
        $this->customerTotalPay($activeSheet,$bill,$dataStartRow,$totalDataRows);
        $groupBills=$this->groupBills($accountBills);
        foreach ($groupBills as $gid=>$gb){
            $groupFirst=true;
            $gbCount=count($gb);
            foreach ($gb as $subBill){

                foreach ($this->dataColumnValue() as $c=>$v){
                    $cellValue=$subBill->$v;
                    if($v=='commission_fee_rate'){
                        $cellValue=$cellValue.'%';
                    }
                    $activeSheet->setCellValue($c.$row,$cellValue);
                }

                //合并
                if($groupFirst){
                    $this->groupData($row,$gbCount,$subBill,$activeSheet);
                }

                $row++;
                $groupFirst=false;
            }
        }
        //居中
        $this->setHorizontal($activeSheet,'a7'.':'.'x'.$row);
    }
    //合并数据
    public function groupData($row,$gbCount,$subBill,$activeSheet){
        $endRow=$row+$gbCount-1;
        foreach ($this->groupColumns() as $gc=>$gcValue){
            $cellValue=$subBill->$gcValue;
            if($gcValue=='charge_standard'){
                $cellValue=str_replace("</br>","\n",$cellValue);
                $activeSheet->getStyle($gc.$row)->getAlignment()->setWrapText(true);
            }
            $activeSheet->mergeCells($gc.$row.':'.$gc.$endRow);
            $activeSheet->setCellValue($gc.$row,$cellValue);
        }
    }
    //单独列
    public function dataColumnValue(){
        return [
            'a'=>'shop_name',
            'b'=>'sub_account',
            'c'=>'service_period',
            'd'=>'service_days',
            'e'=>'consult_quantity',

            'k'=>'overtime_fee',
            'l'=>'sale_acount',
            'm'=>'refund_acount',
            'n'=>'actual_turnover',
            'o'=>'commission_fee_rate',
            'p'=>'commission_fee',

            'r'=>'previous_receivable',
            's'=>'pdd_received',
            't'=>'previous_reserved',
            'u'=>'deposit_cost',
            'v'=>'penalty_amount',

            'x'=>'remark',
        ];
    }
    //分组合并列
    public function groupColumns(){
        return [
            'f'=>'quote_total_consult',
            'g'=>'quote_day_average_consult',
            'h'=>'charge_standard',
            'i'=>'service_quote_final',
            'j'=>'shop_other_fee',

            'q'=>'all_this_period_fee',

//            'w'=>'customer_period_pay',
        ];
    }
    //总计需要付款
    public function customerTotalPay($activeSheet,$bill,$dataStartRow,$totalDataRows){
        $cellValue=$bill->total_customer_pay;
        $activeSheet->mergeCells('w'.$dataStartRow.':'.'w'.($dataStartRow+$totalDataRows-1));
        $activeSheet->setCellValue('w'.$dataStartRow,$cellValue);
    }
    //
    public function bottomInfo($activeSheet,$bill,$totalDataRows){
        $bottomDescRow=5+1+2+$totalDataRows+1+1;
        $activeSheet->mergeCells('a'.$bottomDescRow.':g'.$bottomDescRow);
        $activeSheet->getRowDimension($bottomDescRow)->setRowHeight(80);
        $activeSheet->setCellValue('a'.$bottomDescRow,$this->getBottomDesc($bill));
        $activeSheet->getStyle('a'.$bottomDescRow)->getAlignment()->setWrapText(true);
        $activeSheet->setCellValue('a'.($bottomDescRow+1),'客户签章确认：');
        $activeSheet->setCellValue('a'.($bottomDescRow+2),'日期：');
    }
    //自动列宽
    public function setColumnAutoWidth($activeSheet){
        $columns=['b','d','f'];
        foreach ($columns as $column){
            $activeSheet->getColumnDimension($column)->setAutoSize(true);
        }
    }

    //
    public function setTitleStyle($activeSheet,$cells){
        //所有sheet的表头样式 加粗
        $font = [
            'font' => [
                'bold' => true,
                'size' => 12,
            ],
        ];
        $activeSheet->getStyle($cells)->applyFromArray($font);
        //样式设置 - 水平、垂直居中
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
//                'vertical' => Alignment::VERTICAL_CENTER
            ],
        ];
        $activeSheet->getStyle($cells)->applyFromArray($styleArray);
    }
    //
    public function getBillStatus($status){
        switch ($status){
            case 0:
                return '预账单';
            case 1:
                return '待审核';
            case 2:
                return '正式账单';
            default:
                return '预账单';
        }
    }
    //
    public function getClearStatus($clearStatus){
        switch ($clearStatus){
            case 0:
                return '未结清';
            case 1:
                return '已结清';
            default:
                return '未结清';
        }
    }
    //
    public function groupBills($accountBills){
        $group=[];
        foreach ($accountBills as $ab){
            $group[$ab->quotation_group_id][]=$ab;
        }
        return $group;
    }

    //
    public function getTopDesc($bill){
        $desc="按照本公司财务管理制度要求，需要函证本公司与贵公司(店铺掌柜)的往来账项事项，截止[[日期]]的往来账项列示如下：";
        if($bill->bill_description){
            $descArr=json_decode($bill->bill_description,true);
            $desc=$descArr['top'];
        }
        return str_replace('[[日期]]',$bill->bill_period_end,$desc);
    }
    //
    public function getBottomDesc($bill){
        $desc="注：如贵公司(店铺掌柜)对往来账确认无误，总服务款项应为：[[金额]]；\n此份账单请于收到当月当初6号下午6点前盖章回传或回复账单已确认，如超过期限未回传或未回复则视为我司数据正确无误。\n账单确认后，请于3个工作日内(截止月初9日)结清款项，最晚不得超过月初10号，谢谢配合！\n感谢贵司(店铺掌柜)一直以来的支持和回顾！祝生意兴隆！";
        if($bill->bill_description){
            $descArr=json_decode($bill->bill_description,true);
            $desc=$descArr['bottom'];
        }
        return str_replace('[[金额]]',$bill->total_customer_pay,$desc);
    }



}
